﻿"""
数据库模型
处理数据库连接、表创建和数据操作
"""

import sqlite3
import datetime
import numpy as np
import cv2
from typing import Dict, List, Tuple, Optional
from src.config.settings import DATABASE_CONFIG


class DatabaseManager:
    """数据库管理器"""
    
    def __init__(self):
        self.conn = None
        self.cursor = None
        self.init_database()
    
    def init_database(self):
        """初始化数据库连接和表结构"""
        try:
            self.conn = sqlite3.connect(DATABASE_CONFIG['db_path'])
            self.cursor = self.conn.cursor()
            self.create_tables()
            self.conn.commit()
        except Exception as e:
            print(f"数据库初始化失败: {e}")
            raise
    
    def create_tables(self):
        """创建数据库表"""
        # 创建员工表
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS employees (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                employee_id TEXT UNIQUE NOT NULL,
                face_image BLOB,
                face_features BLOB,
                created_date TEXT NOT NULL
            )
        ''')
        
        # 创建考勤表
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS attendance (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                employee_id TEXT NOT NULL,
                check_in_time TEXT NOT NULL,
                date TEXT NOT NULL,
                status TEXT DEFAULT '正常',
                FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
            )
        ''')
    
    def add_employee(self, name: str, employee_id: str, face_image: np.ndarray, 
                    face_features: np.ndarray) -> bool:
        """添加员工信息"""
        try:
            # 编码图像
            _, img_encoded = cv2.imencode('.jpg', face_image)
            
            # 编码特征
            features_encoded = face_features.tobytes()
            
            # 获取当前时间
            created_date = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            
            # 插入数据
            self.cursor.execute(
                "INSERT INTO employees (name, employee_id, face_image, face_features, created_date) VALUES (?, ?, ?, ?, ?)",
                (name, employee_id, img_encoded.tobytes(), features_encoded, created_date)
            )
            self.conn.commit()
            return True
        except Exception as e:
            print(f"添加员工失败: {e}")
            return False
    
    def get_all_employees(self) -> List[Tuple]:
        """获取所有员工信息"""
        try:
            self.cursor.execute("SELECT name, employee_id, face_image, face_features, created_date FROM employees ORDER BY created_date DESC")
            return self.cursor.fetchall()
        except Exception as e:
            print(f"获取员工信息失败: {e}")
            return []
    
    def get_employee_by_id(self, employee_id: str) -> Optional[Tuple]:
        """根据工号获取员工信息"""
        try:
            self.cursor.execute("SELECT name, employee_id, face_image, face_features FROM employees WHERE employee_id = ?", (employee_id,))
            return self.cursor.fetchone()
        except Exception as e:
            print(f"获取员工信息失败: {e}")
            return None
    
    def check_employee_exists(self, employee_id: str) -> bool:
        """检查员工是否存在"""
        try:
            self.cursor.execute("SELECT COUNT(*) FROM employees WHERE employee_id = ?", (employee_id,))
            return self.cursor.fetchone()[0] > 0
        except Exception as e:
            print(f"检查员工存在性失败: {e}")
            return False
    
    def add_attendance(self, employee_id: str, check_in_time: str, date: str, status: str = "正常") -> bool:
        """添加考勤记录"""
        try:
            self.cursor.execute(
                "INSERT INTO attendance (employee_id, check_in_time, date, status) VALUES (?, ?, ?, ?)",
                (employee_id, check_in_time, date, status)
            )
            self.conn.commit()
            return True
        except Exception as e:
            print(f"添加考勤记录失败: {e}")
            return False
    
    def get_attendance_by_date(self, date: str) -> List[Tuple]:
        """根据日期获取考勤记录"""
        try:
            self.cursor.execute('''
                SELECT e.employee_id, e.name, a.check_in_time, a.status 
                FROM attendance a 
                JOIN employees e ON a.employee_id = e.employee_id 
                WHERE a.date = ?
                ORDER BY a.check_in_time
            ''', (date,))
            return self.cursor.fetchall()
        except Exception as e:
            print(f"获取考勤记录失败: {e}")
            return []
    
    def get_attendance_by_date_range(self, start_date: str, end_date: str) -> List[Tuple]:
        """根据日期范围获取考勤记录"""
        try:
            self.cursor.execute('''
                SELECT e.name, e.employee_id, a.check_in_time, a.date, a.status 
                FROM attendance a 
                JOIN employees e ON a.employee_id = e.employee_id 
                WHERE a.date BETWEEN ? AND ?
                ORDER BY a.date, a.check_in_time
            ''', (start_date, end_date))
            return self.cursor.fetchall()
        except Exception as e:
            print(f"获取考勤记录失败: {e}")
            return []
    
    def check_attendance_exists(self, employee_id: str, date: str) -> bool:
        """检查某员工某天是否已打卡"""
        try:
            self.cursor.execute("SELECT COUNT(*) FROM attendance WHERE employee_id = ? AND date = ?", (employee_id, date))
            return self.cursor.fetchone()[0] > 0
        except Exception as e:
            print(f"检查考勤记录失败: {e}")
            return False
    
    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
    
    def __del__(self):
        """析构函数"""
        self.close()
